﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using ERP_RORZE_DLL.Product.database;
using ERP_RORZE_DLL.Product.DAO;
using System.Data.OleDb;
using ERP_RORZE_SYSTEM;
using System.Data.Common;
using System.Data.SqlClient;
using ERP_RORZE_DLL.Product.Base;

namespace ERP_RORZE_PRODUCT.Product.PartData
{
    public partial class ctrlOptionData : DevExpress.XtraEditors.XtraUserControl
    {
        ClsOptionDataTable obj = new ClsOptionDataTable();
        ClsPartDataDAO clspartDAO = new ClsPartDataDAO();
        DataTable dt;

        public ctrlOptionData()
        {
            InitializeComponent();
        }

        void Re_load(object sender, EventArgs e)
        {
            dt = clspartDAO.GetAllOptionData();
            dgvOptionData.DataSource = dt;
            gridView1.BestFitColumns();
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            Frm_OptionDataAdd frmAdd = new Frm_OptionDataAdd();
            frmAdd.Re_Load += new EventHandler(Re_load);
            frmAdd.ShowDialog();
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            try
            {
                Frm_OptionDataEdit frmEdit = new Frm_OptionDataEdit();
                frmEdit.ID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "ID").ToString();
                frmEdit.AondNote = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "AondNote").ToString();
                frmEdit.ClampTime = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "ClampTime").ToString();
                frmEdit.CLUpdateday = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "CLUpdateday").ToString();
                frmEdit.InputDate = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "InputDate").ToString();
                frmEdit.JigID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "JigID").ToString();
                frmEdit.LastOption = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "LastOption").ToString();
                frmEdit.MachineID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "MachineID").ToString();
                frmEdit.Memo = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Memo").ToString();
                frmEdit.Note = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Note").ToString();
                frmEdit.OptionID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "OptionID").ToString();
                frmEdit.PartID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "PartID").ToString();
                frmEdit.ProTime = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "ProTime").ToString();
                frmEdit.StaffID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "StaffID").ToString();
                frmEdit.TechDate = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "TechDate").ToString();
                frmEdit.ToolQty = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "ToolQty").ToString();
                frmEdit.UpdateDay = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "UpdateDay").ToString();
                frmEdit.Jig = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Jig").ToString();
                frmEdit.JigType = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "JigType").ToString();
                frmEdit.Re_Load += new EventHandler(Re_load);
                frmEdit.ShowDialog();
            }
            catch
            {
            }
        }

        private void btnReload_Click(object sender, EventArgs e)
        {
            Re_load(sender, e);
        }

        private void dgvOptionData_Load(object sender, EventArgs e)
        {
            Re_load(sender, e);
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            obj.ID = int.Parse(gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "ID").ToString());
            clspartDAO.DeleteOptionData(obj);
            Re_load(sender, e);

        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            Re_load(sender, e);
        }

        private void btnAddNew_Click(object sender, EventArgs e)
        {
            btnAdd_Click(sender, e);
        }

        private void btEdit_Click(object sender, EventArgs e)
        {
            btnEdit_Click(sender, e);
        }

        private void btDelete_Click(object sender, EventArgs e)
        {
            btnDelete_Click(sender, e);
        }

        private void simpleButton1_Click(object sender, EventArgs e)
        {
            FileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection connection = new OleDbConnection();
                try
                {
                    string path = fileDialog.FileName;
                    INISettings iniSettings = new INISettings();
                    string connectString = iniSettings.SQLConnectionString;
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    // Bulk Copy to SQL Server 
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
                    bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_OptionData]";

                    SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("InputDate", "InputDate");
                    bulkInsert.ColumnMappings.Add(date);

                    SqlBulkCopyColumnMapping toolId = new SqlBulkCopyColumnMapping("PartID", "PartID");
                    bulkInsert.ColumnMappings.Add(toolId);

                    SqlBulkCopyColumnMapping makerId = new SqlBulkCopyColumnMapping("MachineID", "MachineID");
                    bulkInsert.ColumnMappings.Add(makerId);

                    SqlBulkCopyColumnMapping PartID = new SqlBulkCopyColumnMapping("OptionID", "OptionID");
                    bulkInsert.ColumnMappings.Add(PartID);

                    SqlBulkCopyColumnMapping Qty = new SqlBulkCopyColumnMapping("LastOption", "LastOption");
                    bulkInsert.ColumnMappings.Add(Qty);

                    SqlBulkCopyColumnMapping Deadline = new SqlBulkCopyColumnMapping("JigID", "JigID");
                    bulkInsert.ColumnMappings.Add(Deadline);

                    SqlBulkCopyColumnMapping RawQty = new SqlBulkCopyColumnMapping("ToolQty", "ToolQty");
                    bulkInsert.ColumnMappings.Add(RawQty);

                    SqlBulkCopyColumnMapping HelisertQty = new SqlBulkCopyColumnMapping("ProTime", "ProTime");
                    bulkInsert.ColumnMappings.Add(HelisertQty);

                    SqlBulkCopyColumnMapping BlastQty = new SqlBulkCopyColumnMapping("ClampTime", "ClampTime");
                    bulkInsert.ColumnMappings.Add(BlastQty);

                    SqlBulkCopyColumnMapping MONo = new SqlBulkCopyColumnMapping("TechDate", "TechDate");
                    bulkInsert.ColumnMappings.Add(MONo);

                    SqlBulkCopyColumnMapping MOQty = new SqlBulkCopyColumnMapping("UpdateDay", "UpdateDay");
                    bulkInsert.ColumnMappings.Add(MOQty);

                    SqlBulkCopyColumnMapping Started = new SqlBulkCopyColumnMapping("StaffID", "StaffID");
                    bulkInsert.ColumnMappings.Add(Started);

                    SqlBulkCopyColumnMapping Finished = new SqlBulkCopyColumnMapping("Note", "Note");
                    bulkInsert.ColumnMappings.Add(Finished);

                    SqlBulkCopyColumnMapping FinishDate = new SqlBulkCopyColumnMapping("AondNote", "AondNote");
                    bulkInsert.ColumnMappings.Add(FinishDate);

                    SqlBulkCopyColumnMapping Change = new SqlBulkCopyColumnMapping("Memo", "Memo");
                    bulkInsert.ColumnMappings.Add(Change);

                    SqlBulkCopyColumnMapping ChangeDate = new SqlBulkCopyColumnMapping("CLUpdateday", "CLUpdateday");
                    bulkInsert.ColumnMappings.Add(ChangeDate);

                    SqlBulkCopyColumnMapping Imported = new SqlBulkCopyColumnMapping("JigType", "JigType");
                    bulkInsert.ColumnMappings.Add(Imported);

                    SqlBulkCopyColumnMapping ImportFrom = new SqlBulkCopyColumnMapping("Jig", "Jig");
                    bulkInsert.ColumnMappings.Add(ImportFrom);

                    bulkInsert.WriteToServer(dr);

                    connection.Close();
                    Re_load(sender, e);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    connection.Close();
                }
            }
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            clsConstantsFuction.ExportToExcel(gridView1, 1, true);
        }
    }
}
